package org.davepkxxx.jdbc

import org.springframework.jdbc.core.support.JdbcDaoSupport
import org.springframework.jdbc.core._
import javax.sql.DataSource
import java.sql.{Statement, PreparedStatement, Connection, ResultSet}
import scala.collection.{mutable, JavaConversions}
import org.springframework.dao.DataAccessException
import org.springframework.jdbc.support.{GeneratedKeyHolder, KeyHolder}
import org.springframework.jdbc.core.namedparam.{MapSqlParameterSource, NamedParameterJdbcOperations, NamedParameterJdbcTemplate}
import scala.beans.BeanProperty

/**
 * Convenient super class for JDBC-based data access objects in Scala.
 * Created at 1/13/14 11:28 PM.
 * @author David Dai
 */
class ScalaJdbcDaoSupport extends JdbcDaoSupport {

  @BeanProperty var scalaJdbcTemplate: ScalaJdbcTemplate = _

  override def initTemplateConfig: Unit = this.scalaJdbcTemplate = new ScalaJdbcTemplate(getJdbcTemplate)

}

/**
 * Interface specifying a basic set of JDBC operations.
 * Created at 1/13/14 11:28 PM.
 * @author David Dai
 */
trait ScalaJdbcOperations {

  /**
   * Expose the classic Spring JdbcTemplate to allow invocation of classic JDBC operations.
   * @return the classic Spring JdbcTemplate.
   */
  def getJdbcOperations: JdbcOperations

  /**
   * Expose the classic NamedParameterJdbcOperations to allow invocation of classic Scala JDBC operations.
   * @return the classic NamedParameterJdbcOperations.
   */
  def getNamedParameterJdbcOperations: NamedParameterJdbcOperations

  /**
   * Execute a query given static SQL, mapping each row to a Scala object via a map function.
   * @param sql  SQL query to execute
   * @param func object that will map one object per row
   * @tparam T   type of the mapped object
   * @throws DataAccessException if there is any problem executing the query
   * @return the result list, containing mapped objects
   */
  @throws(classOf[DataAccessException])
  def query[T](sql: String, func: ((ResultSet, Int) => T)): mutable.Buffer[T]

  /**
   * Execute a query given static SQL, mapping each row to a Scala object via a map function, binding the given arguments.
   * @param sql  SQL query to execute
   * @param func object that will map one object per row
   * @param args arguments to bind to the query
   * @tparam T   type of the mapped object
   * @throws DataAccessException if there is any problem executing the query
   * @return the result list, containing mapped objects
   */
  @throws(classOf[DataAccessException])
  def query[T](sql: String, func: ((ResultSet, Int) => T), args: Array[_]): mutable.Buffer[T]

  /**
   * Execute a query given static SQL, mapping each row to a Scala object via a map function, binding the given arguments.
   * @param sql      SQL query to execute
   * @param func     object that will map one object per row
   * @param paramMap arguments to bind to the query
   * @tparam T   type of the mapped object
   * @throws DataAccessException if there is any problem executing the query
   * @return the result list, containing mapped objects
   */
  @throws(classOf[DataAccessException])
  def query[T](sql: String, func: ((ResultSet, Int) => T), paramMap: mutable.Map[String, _]): mutable.Buffer[T]

  /**
   * Execute a query given static SQL, mapping a single result row to a Scala object via a map function.
   * @param sql  SQL query to execute
   * @param func object that will map one object per row
   * @tparam T   type of the mapped object
   * @throws DataAccessException if there is any problem executing the query
   * @return the single mapped object
   */
  @throws(classOf[DataAccessException])
  def queryForObject[T](sql: String, func: ((ResultSet, Int) => T)): T

  /**
   * Execute a query given static SQL, mapping a single result row to a Scala object via a map function, binding the given arguments.
   * @param sql  SQL query to execute
   * @param func object that will map one object per row
   * @param args arguments to bind to the query
   * @tparam T   type of the mapped object
   * @throws DataAccessException if there is any problem executing the query
   * @return the single mapped object
   */
  @throws(classOf[DataAccessException])
  def queryForObject[T](sql: String, func: ((ResultSet, Int) => T), args: Array[_]): T

  /**
   * Execute a query given static SQL, mapping a single result row to a Scala object via a map function, binding the given arguments.
   * @param sql      SQL query to execute
   * @param func     object that will map one object per row
   * @param paramMap arguments to bind to the query
   * @tparam T   type of the mapped object
   * @throws DataAccessException if there is any problem executing the query
   * @return the single mapped object
   */
  @throws(classOf[DataAccessException])
  def queryForObject[T](sql: String, func: ((ResultSet, Int) => T), paramMap: mutable.Map[String, _]): T

  /**
   * Issue a single SQL update operation (such as an insert, update or delete statement), binding the given arguments.
   * @param sql  SQL containing bind parameters
   * @param args arguments to bind to the query
   * @throws DataAccessException if there is any problem.
   * @return the number of rows affected
   */
  @throws(classOf[DataAccessException])
  def update(sql: String, args: Array[_]): Int

  /**
   * Issue a single SQL update operation (such as an insert, update or delete statement), binding the given arguments.
   * @param sql      SQL containing bind parameters
   * @param paramMap arguments to bind to the query
   * @throws DataAccessException if there is any problem.
   */
  @throws(classOf[DataAccessException])
  def update(sql: String, paramMap: mutable.Map[String, _]): Int

  @throws(classOf[DataAccessException])
  def updateForKey(sql: String, keyColumnNames: Array[String]): KeyHolder

  @throws(classOf[DataAccessException])
  def updateForKey(sql: String, args: Array[_], keyColumnNames: Array[String]): KeyHolder

  @throws(classOf[DataAccessException])
  def updateForKey(sql: String, paramMap: mutable.Map[String, _], keyColumnNames: Array[String]): KeyHolder

  @throws(classOf[DataAccessException])
  def save(sql: String): KeyHolder

  @throws(classOf[DataAccessException])
  def save(sql: String, args: Array[_]): KeyHolder

  @throws(classOf[DataAccessException])
  def save(sql: String, paramMap: mutable.Map[String, _]): KeyHolder

}

/**
 * Template class with a basic set of JDBC operations.
 * Created at 1/13/14 11:28 PM.
 * @param classicJdbcTemplate the classic Spring JdbcTemplate to wrap
 * @author David Dai
 */
class ScalaJdbcTemplate(classicJdbcTemplate: JdbcOperations) extends ScalaJdbcOperations {

  private val classicNamedParameterJdbcOperations = new NamedParameterJdbcTemplate(classicJdbcTemplate)

  /**
   * Template class with a basic set of JDBC operations, no data source.
   */
  def this() = this(new JdbcTemplate)

  /**
   * Template class with a basic set of JDBC operations.
   * @param dataSource the JDBC DataSource to obtain connections from
   */
  def this(dataSource: DataSource) = this(new JdbcTemplate(dataSource))

  override def getJdbcOperations: JdbcOperations = classicJdbcTemplate

  override def getNamedParameterJdbcOperations: NamedParameterJdbcOperations = classicNamedParameterJdbcOperations

  @throws(classOf[DataAccessException])
  override def query[T](sql: String, func: ((ResultSet, Int) => T)): mutable.Buffer[T] =
    JavaConversions.asScalaBuffer(getJdbcOperations.query(sql, new ScalaRowMapper[T](func)))

  @throws(classOf[DataAccessException])
  override def query[T](sql: String, func: ((ResultSet, Int) => T), args: Array[_]): mutable.Buffer[T] =
    JavaConversions.asScalaBuffer(getJdbcOperations.query(sql, args.asInstanceOf[Array[Object]], new ScalaRowMapper[T](func)))

  @throws(classOf[DataAccessException])
  override def query[T](sql: String, func: ((ResultSet, Int) => T), paramMap: mutable.Map[String, _]): mutable.Buffer[T] =
    JavaConversions.asScalaBuffer(getNamedParameterJdbcOperations.query(sql,
      paramMap.asInstanceOf[java.util.Map[String, _]], new ScalaRowMapper[T](func)))

  @throws(classOf[DataAccessException])
  override def queryForObject[T](sql: String, func: ((ResultSet, Int) => T)): T =
    getJdbcOperations.queryForObject(sql, new ScalaRowMapper[T](func))

  @throws(classOf[DataAccessException])
  override def queryForObject[T](sql: String, func: ((ResultSet, Int) => T), args: Array[_]): T =
    getJdbcOperations.queryForObject(sql, args.asInstanceOf[Array[Object]], new ScalaRowMapper[T](func))

  @throws(classOf[DataAccessException])
  override def queryForObject[T](sql: String, func: ((ResultSet, Int) => T), paramMap: mutable.Map[String, _]): T =
    getNamedParameterJdbcOperations.queryForObject(sql, paramMap.asInstanceOf[java.util.Map[String, _]], new ScalaRowMapper[T](func))

  @throws(classOf[DataAccessException])
  override def update(sql: String, args: Array[_]): Int =
    getJdbcOperations.update(sql, new ArgumentPreparedStatementSetter(args.asInstanceOf[Array[Object]]))

  @throws(classOf[DataAccessException])
  override def update(sql: String, paramMap: mutable.Map[String, _]): Int =
    getJdbcOperations.update(sql, paramMap.asInstanceOf[java.util.Map[String, _]])

  @throws(classOf[DataAccessException])
  override def updateForKey(sql: String, keyColumnNames: Array[String]): KeyHolder = {
    val key = new GeneratedKeyHolder
    getJdbcOperations.update(new PreparedStatementCreator {
      def createPreparedStatement(con: Connection): PreparedStatement = con.prepareStatement(sql, keyColumnNames)
    }, key)
    key
  }

  @throws(classOf[DataAccessException])
  override def updateForKey(sql: String, args: Array[_], keyColumnNames: Array[String]): KeyHolder = {
    val key = new GeneratedKeyHolder
    getJdbcOperations.update(new PreparedStatementCreator {
      def createPreparedStatement(con: Connection): PreparedStatement = {
        val ps  = con.prepareStatement(sql, keyColumnNames)
        val pss = new ArgumentPreparedStatementSetter(args.asInstanceOf[Array[Object]])
        try { if (pss ne null) { pss.setValues(ps) } }
        finally { pss.asInstanceOf[ParameterDisposer].cleanupParameters() }
        ps
      }
    }, key)
    key
  }

  @throws(classOf[DataAccessException])
  override def updateForKey(sql: String, paramMap: mutable.Map[String, _], keyColumnNames: Array[String]): KeyHolder = {
    val key         = new GeneratedKeyHolder
    val paramSource = new MapSqlParameterSource(paramMap.asInstanceOf[java.util.Map[String, _]])
    getNamedParameterJdbcOperations.update(sql, paramSource, key, keyColumnNames)
    key
  }

  @throws(classOf[DataAccessException])
  def save(sql: String): KeyHolder = {
    val key = new GeneratedKeyHolder
    getJdbcOperations.update(new PreparedStatementCreator {
      def createPreparedStatement(con: Connection): PreparedStatement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
    }, key)
    key
  }

  @throws(classOf[DataAccessException])
  override def save(sql: String, args: Array[_]): KeyHolder = {
    val key = new GeneratedKeyHolder
    getJdbcOperations.update(new PreparedStatementCreator {
      def createPreparedStatement(con: Connection): PreparedStatement = {
        val ps  = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)
        val pss = new ArgumentPreparedStatementSetter(args.asInstanceOf[Array[Object]])
        try { if (pss ne null) { pss.setValues(ps) } }
        finally { pss.asInstanceOf[ParameterDisposer].cleanupParameters() }
        ps
      }
    }, key)
    key
  }

  @throws(classOf[DataAccessException])
  override def save(sql: String, paramMap: mutable.Map[String, _]): KeyHolder = {
    val key = new GeneratedKeyHolder
    val paramSource = new MapSqlParameterSource(paramMap.asInstanceOf[java.util.Map[String, _]])
    getNamedParameterJdbcOperations.update(sql, paramSource, key)
    key
  }

}

class ScalaRowCallbackHandler[T](processFunction: ((ResultSet, Int) => T)) extends RowCountCallbackHandler {

  private val rowMapper = new ScalaRowMapper[T](processFunction)

  val results: mutable.Buffer[T] = mutable.ArrayBuffer[T]()

  override def processRow(rs: ResultSet, rowNum: Int): Unit = { results += rowMapper.mapRow(rs, rowNum) }

}

/**
 * A class used by ScalaJdbcTemplate for mapping rows of a ResultSet on a per-row basis.
 * @param mapFunction object that will map one object per row
 * @tparam T          type of the mapped object
 */
class ScalaRowMapper[T](mapFunction: ((ResultSet, Int) => T)) extends RowMapper[T] {

  def mapRow(rs: ResultSet, rowNum: Int): T = mapFunction(rs, rowNum)

}
